﻿using SwenAssigment.Classes;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;

namespace SwenAssigment.DBClasses
{
    public class Duty
    {
        private static SqlConnection scon()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            return conn;
        }
        public static ArrayList WeekDuty(String Week)
        {
            string[] dates = Week.Split('~');
            ArrayList Dutys = new ArrayList();
            SqlConnection conn = scon();
            //DateTime.Compare(k, start) >= 0 && DateTime.Compare(k, end) <= 0
            using (SqlCommand comm = new SqlCommand("SELECT * from [Duty_Schedule] where datediff(day,Date, Convert(Date,'" + dates[0] + "',104))<=0 and datediff(day,Date, Convert(Date,'" + dates[1] + "',104))>=0", conn))
            {
                conn.Open();
                using (SqlDataReader dr = comm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        //Object[] values = new Object[dr.FieldCount];
                        //int fieldCount = dr.GetValues(values);
                        DutyDetail DutyList = new DutyDetail();
                        DutyList.DutyId = (Int32)dr[0];
                        DutyList.Date = (DateTime)dr[1];
                        //conn.Open();
                        Dutys.Add(DutyList);

                    }
                }
                conn.Close();
            }

            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select RoomView.[Staff Name] from RoomM_Duty,RoomView where RoomM_Duty.StaffId = RoomView.Staff_ID and RoomM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.RoomList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select GeneralView.[Staff Name] from GeneralM_Duty,GeneralView where GeneralM_Duty.StaffId = GeneralView.Staff_ID and GeneralM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.GenList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select EstateView.[Staff Name] from EstateM_Duty,EstateView where EstateM_Duty.StaffId = EstateView.Staff_ID and EstateM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.EstateList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select SecurityView.[Staff Name] from SecurityM_Duty,SecurityView where SecurityM_Duty.StaffId = SecurityView.Staff_ID and SecurityM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.SecurityList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            return Dutys;
        }
        public static ArrayList MonthDuty(int month, int year)
        {
            ArrayList Dutys = new ArrayList();
            SqlConnection conn = scon();
            using (SqlCommand comm = new SqlCommand("SELECT * from [Duty_Schedule] where Month(Date)=" + month + " and Year(Date)=" + year, conn))
            {
                conn.Open();
                using (SqlDataReader dr = comm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        //Object[] values = new Object[dr.FieldCount];
                        //int fieldCount = dr.GetValues(values);
                        DutyDetail DutyList = new DutyDetail();
                        DutyList.DutyId = (Int32)dr[0];
                        DutyList.Date = (DateTime)dr[1];
                        //conn.Open();
                        Dutys.Add(DutyList);

                    }
                }
                conn.Close();
            }

            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select RoomView.[Staff Name] from RoomM_Duty,RoomView where RoomM_Duty.StaffId = RoomView.Staff_ID and RoomM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.RoomList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select GeneralView.[Staff Name] from GeneralM_Duty,GeneralView where GeneralM_Duty.StaffId = GeneralView.Staff_ID and GeneralM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.GenList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select EstateView.[Staff Name] from EstateM_Duty,EstateView where EstateM_Duty.StaffId = EstateView.Staff_ID and EstateM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.EstateList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select SecurityView.[Staff Name] from SecurityM_Duty,SecurityView where SecurityM_Duty.StaffId = SecurityView.Staff_ID and SecurityM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.SecurityList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            return Dutys;
        }
        public static ArrayList ADuty(String Date)
        {
            ArrayList Dutys = new ArrayList();
            SqlConnection conn = scon();
            using (SqlCommand comm = new SqlCommand("SELECT * from [Duty_Schedule] where datediff(day,Date, Convert(Date,'" + Date + "',104))=0", conn))
            {
                conn.Open();
                using (SqlDataReader dr = comm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        //Object[] values = new Object[dr.FieldCount];
                        //int fieldCount = dr.GetValues(values);
                        DutyDetail DutyList = new DutyDetail();
                        DutyList.DutyId = (Int32)dr[0];
                        DutyList.Date = (DateTime)dr[1];
                        //conn.Open();
                        Dutys.Add(DutyList);

                    }
                }
                conn.Close();
            }

            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select RoomView.[Staff Name] from RoomM_Duty,RoomView where RoomM_Duty.StaffId = RoomView.Staff_ID and RoomM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.RoomList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select GeneralView.[Staff Name] from GeneralM_Duty,GeneralView where GeneralM_Duty.StaffId = GeneralView.Staff_ID and GeneralM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.GenList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select EstateView.[Staff Name] from EstateM_Duty,EstateView where EstateM_Duty.StaffId = EstateView.Staff_ID and EstateM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.EstateList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select SecurityView.[Staff Name] from SecurityM_Duty,SecurityView where SecurityM_Duty.StaffId = SecurityView.Staff_ID and SecurityM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.SecurityList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            return Dutys;
        }
        public static ArrayList AllDuty(int year)
        {
            ArrayList Dutys = new ArrayList();
            SqlConnection conn = scon();
            using (SqlCommand comm = new SqlCommand("SELECT * from [Duty_Schedule] where year(Date) =" + year + "order by Date Desc", conn))
            {
                conn.Open();
                using (SqlDataReader dr = comm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        //Object[] values = new Object[dr.FieldCount];
                        //int fieldCount = dr.GetValues(values);
                        DutyDetail DutyList = new DutyDetail();
                        DutyList.DutyId = (Int32)dr[0];
                        DutyList.Date = (DateTime)dr[1];
                        //conn.Open();
                        Dutys.Add(DutyList);

                    }
                }
                conn.Close();
            }

            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select RoomView.[Staff Name] from RoomM_Duty,RoomView where RoomM_Duty.StaffId = RoomView.Staff_ID and RoomM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.RoomList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select GeneralView.[Staff Name] from GeneralM_Duty,GeneralView where GeneralM_Duty.StaffId = GeneralView.Staff_ID and GeneralM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.GenList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select EstateView.[Staff Name] from EstateM_Duty,EstateView where EstateM_Duty.StaffId = EstateView.Staff_ID and EstateM_Duty.RefDutyId=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.EstateList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            foreach (DutyDetail a in Dutys)
            {
                using (SqlCommand comm = new SqlCommand("select SecurityView.[Staff Name] from SecurityM_Duty,SecurityView where SecurityM_Duty.StaffId = SecurityView.Staff_ID and SecurityM_Duty.RefDuty=" + a.DutyId, conn))
                {
                    conn.Open();
                    using (SqlDataReader dr = comm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            a.SecurityList += (String)dr[0] + Environment.NewLine;

                        }
                    }
                    conn.Close();
                }
            }
            return Dutys;
        }
        public static String CreateDuty(DateTime date, ListBox gen, ListBox room, ListBox estate, ListBox security)
        {
            int workon = 0;
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("InsertDutyProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@Date", date));
            cmd.Parameters.AddRange(parameters.ToArray());
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read() == true)
            {
                try
                {
                    workon = Convert.ToInt32(rdr[0].ToString());
                }
                catch (Exception ex)
                {
                    return "Date Existed";
                }
            }
            conn.Close();
            if (workon != 0)
            {
                foreach (ListItem s in gen.Items)
                {
                    //conn = scon();
                    cmd = new SqlCommand("AssignUserProcedure", conn);
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    parameters = new List<SqlParameter>();
                    parameters.Add(new SqlParameter("@choice", 1));
                    parameters.Add(new SqlParameter("@data", s.Value));
                    parameters.Add(new SqlParameter("@ref", workon));
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                foreach (ListItem s in estate.Items)
                {
                    //conn = scon();
                    cmd = new SqlCommand("AssignUserProcedure", conn);
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    parameters = new List<SqlParameter>();
                    parameters.Add(new SqlParameter("@choice", 2));
                    parameters.Add(new SqlParameter("@data", s.Value));
                    parameters.Add(new SqlParameter("@ref", workon));
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                foreach (ListItem s in room.Items)
                {
                    //conn = scon();
                    cmd = new SqlCommand("AssignUserProcedure", conn);
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    parameters = new List<SqlParameter>();
                    parameters.Add(new SqlParameter("@choice", 3));
                    parameters.Add(new SqlParameter("@data", s.Value));
                    parameters.Add(new SqlParameter("@ref", workon));
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                foreach (ListItem s in security.Items)
                {
                    //conn = scon();
                    cmd = new SqlCommand("AssignUserProcedure", conn);
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    parameters = new List<SqlParameter>();
                    parameters.Add(new SqlParameter("@choice", 4));
                    parameters.Add(new SqlParameter("@data", s.Value));
                    parameters.Add(new SqlParameter("@ref", workon));
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            return "ok";
        }

    }
}